import java.sql.*;
import java.util.Scanner;

public class Main {
    public static void main(String[] args) {
        m1();
        m2();
        m3();
        m4();
        m5();

    }
    private static void m1(){
        String url="jdbc:mysql://localhost:3306/jdbc";
        String user="root";
        String password="050810";
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection(url,user,password);

            String sql="SELECT  * FROM user";
            pstmt=conn.prepareStatement(sql);
            rs=pstmt.executeQuery();
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String pass = rs.getString(3);
                System.out.println(id+"---"+name+"---"+pass);

            }


        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try{
                if(rs!=null) rs.close();
                if(pstmt!=null) pstmt.close();
                if(conn!=null) conn.close();

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    private static void m2(){
        String url="jdbc:mysql://localhost:3306/jdbc";
        String user="root";
        String password="050810";
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {
            Scanner sc=new Scanner(System.in);
            System.out.println("请输入你想查询的姓名：");

            String word = sc.nextLine();

            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection(url,user,password);

            String sql="SELECT  * FROM user where name like ?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setObject(1,"%"+word+"%");
            rs=pstmt.executeQuery();
            while(rs.next()){
                int id = rs.getInt(1);
                String name = rs.getString(2);
                String pass = rs.getString(3);
                System.out.println(id+"---"+name+"---"+pass);

            }



        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try{
                if(rs!=null) rs.close();
                if(pstmt!=null) pstmt.close();
                if(conn!=null) conn.close();

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    private static void m3(){
        String url="jdbc:mysql://localhost:3306/jdbc";
        String user="root";
        String password="050810";
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection(url,user,password);

            String sql="insert into user(name,pass) value(?,?)";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,"wangwu");
            pstmt.setString(2,"123456");
            int insert=pstmt.executeUpdate();
            if(insert>0)
            {
                System.out.println("插入成功");
            }
            else
            {
                System.out.println("插入失败");
            }



        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try{
                if(rs!=null){
                    rs.close();
                }
                if(pstmt!=null)  {
                    pstmt.close();
                }
                if(conn!=null)
                {
                    conn.close();
                }

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

    }
    private static void m4(){

        String url="jdbc:mysql://localhost:3306/jdbc";
        String user="root";
        String password="050810";
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection(url,user,password);

            String sql="update user set name = ?,pass = ? where id = ?";
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,"wangwu");
            pstmt.setString(2,"123456");
            pstmt.setInt(3,1);
            int insert=pstmt.executeUpdate();
            if(insert>0)
            {
                System.out.println("修改成功");
            }
            else
            {
                System.out.println("修改失败");
            }



        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try{
                if(rs!=null){
                    rs.close();
                }
                if(pstmt!=null)  {
                    pstmt.close();
                }
                if(conn!=null)
                {
                    conn.close();
                }

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    private static void m5(){

        String url="jdbc:mysql://localhost:3306/jdbc";
        String user="root";
        String password="050810";
        Connection conn=null;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try {

            Class.forName("com.mysql.cj.jdbc.Driver");
            conn=DriverManager.getConnection(url,user,password);

            String sql="delete FROM user where id = ?";
            pstmt=conn.prepareStatement(sql);
           pstmt.setObject(1,7);
            int insert=pstmt.executeUpdate();
            if(insert>0)
            {
                System.out.println("删除成功");
            }
            else
            {
                System.out.println("删除失败");
            }



        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            try{
                if(rs!=null){
                    rs.close();
                }
                if(pstmt!=null)  {
                    pstmt.close();
                }
                if(conn!=null)
                {
                    conn.close();
                }

            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
}
